package org.jeecg.modules.veterans.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.system.entity.SysVeteranSta;
import org.jeecg.modules.system.vo.SysNameValueVo;
import org.jeecg.modules.veterans.entity.SysVeterans;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * @Description: 退役军人信息表
 * @Author: jeecg-boot
 * @Date:   2022-03-27
 * @Version: V1.0
 */
public interface SysVeteransMapper extends BaseMapper<SysVeterans> {

    public SysVeterans selectVeteransByPhone(String phone);

    public SysVeterans selectVeteransBySfzh(String sfzh);

    @Select("SELECT * FROM\n" +
            "\t( SELECT COUNT(*) AS certificationCount FROM sys_veterans WHERE status = 2 ) AS certificationCount,\n" +
            "\t( SELECT COUNT(*) AS manCount FROM sys_veterans WHERE  sex = '男' ) AS manCount,\n" +
            "\t( SELECT COUNT(*) AS total FROM sys_veterans) AS total")
    SysVeteranSta veteranSta();

    @Select("SELECT \n" +
            "    CASE \n" +
            "        WHEN age >= 60 THEN '60以上'\n" +
            "        WHEN age >= 45 AND age < 60 THEN '45-60'\n" +
            "        WHEN age >= 30 AND age < 45 THEN '30-45'\n" +
            "        WHEN age >= 18 AND age < 30 THEN '18-30'\n" +
            "        ELSE '18以下'\n" +
            "    END AS label,\n" +
            "    COUNT(*) AS value\n" +
            "FROM\n" +
            "    sys_veterans\n" +
            "GROUP BY\n" +
            "    label\n" +
            "ORDER BY\n" +
            "    FIELD(label, '60以上', '45-60', '30-45', '18-30', '18以下');")
    List<SysNameValueVo> veteranAgeSta();
}
